import akshare as ak
from connector import mysqlConnectorHelper as mc
import datetime
import SimpleMySqlClass as smc


# 获取数据库链接
def get_mysql_conn():
    conn = smc.SimpleMySqlClass("127.0.0.1", "stocks", "haojg", "haojg")
    return conn


def get_data_and_read(code):
    # 拉取远端数据
    stock_qfq_data = get_stock_data(code)
    # 获取链接
    conn = get_mysql_conn()
    # 落库
    save_data_to_mysql(conn, code, stock_qfq_data)
    # 读数
    read_data_from_mysql(conn)
    # 关闭连接
    conn.close()


# date_str = "2022-01-01"
# date_obj = datetime.strptime(date_str, "%Y-%m-%d")
# datetime.datetime.strptime("2019-05-10 00:00:55", "%Y-%m-%d %H:%M:%S")
# str_p = "2019-01-30 15:29:08"
# dateTime_p = datetime.datetime.strptime(str_p, "%Y-%m-%d %H:%M:%S")
# from datetime import datetime, timedelta
# import calendar

# current_date = datetime.now()
# next_month_end = current_date + timedelta(
#     days=calendar.monthrange(current_date.year, current_date.month)[1]
# )
# next_month = datetime(next_month_end.year, next_month_end.month, 1)
# print(next_month)
# from datetime import datetime, timedelta
# # 当前日期
# current_date = datetime.now()
# # 减去一个月
# one_month_ago = current_date - timedelta(days=30)
# print("当前日期:", current_date)
# print("一个月前的日期:", one_month_ago)


# 1, get akshare stock data
def get_stock_data(code):
    end_date = datetime.datetime.now()
    start_date = end_date - datetime.timedelta(days=395)
    start_date_param = start_date.strftime("%Y%m%d")
    end_date_param = end_date.strftime("%Y%m%d")

    stock_qfq_data = ak.stock_zh_a_hist(
        symbol=code, adjust="qfq", start_date=start_date_param, end_date=end_date_param
    )
    # print(stock_qfq_data)
    stock_qfq_data = stock_qfq_data.iloc[:, 0:7]

    # 日期     开盘     收盘     最高     最低     成交量  成交额
    # 列名记得这样定义好
    stock_qfq_data.columns = [
        "date",
        "open",
        "close",
        "high",
        "low",
        "volume",
        "amount",
    ]
    stock_qfq_data["date_time"] = stock_qfq_data.loc[:, ["date"]]
    stock_qfq_data["code"] = code
    print(stock_qfq_data)
    return stock_qfq_data


# 2, save data to mysql
def save_data_to_mysql(conn, code, stock_data_df):

    for index, row in stock_data_df.iterrows():
        # 插入新记录
        data = {
            "id": 0,
            "date": row["date"],
            "date_time": row["date_time"],
            "code": code,
            "open": row["open"],
            "high": row["high"],
            "low": row["low"],
            "close": row["close"],
            "volume": row["volume"],
            "amount": row["amount"],
            "create_time": datetime.datetime.now(),
            "update_time": datetime.datetime.now(),
        }
        # rs = conn.insert("t_stock_k_data", data)
        # print("rs = ", rs)

        table = "t_stock_data_temp"
        rs = conn.insert(table, data)
        # print("rs = ", rs)

    print("-------------------------------")
    return


# 3, query data from mysql
def read_data_from_mysql(conn):
    tableName = "t_stock_data_temp"
    # sql = f"select date, code, open, high, low, close, volume, amount from {tableName}"
    sql = f"select count(*) from {tableName}"
    rs = conn.get_one(sql)
    print("query rs = ", rs)
    pass


if __name__ == "__main__":
    code = "002202"
    get_data_and_read(code)
    print("main")
